﻿Imports System.Math
Imports System.DBNull
Imports System.Data
Imports System.Data.SqlClient
Imports Conexao_liion

Partial Class CustoDeImportacaoDados
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        'Faz com que ao sair do aplicativo, limpe o buffer e não podendo entrar na página através do butão voltar do Browser.
        Response.Cache.SetCacheability(HttpCacheability.ServerAndNoCache)
        'Fim

        Response.Cache.SetExpires(DateTime.Now.AddSeconds(1))

        If Not Page.IsPostBack Then

            Dim strConnection As String
            strConnection = "A"

            lblModulo.Text = "Operações"
            lblModuloItem.Text = "Dados da Importação"

            If (LoadHolding(strConnection) = True) Then

                FillDDL()

                Try
                    If (Session("strOperacao") = "I") Then

                    ElseIf (Session("strOperacao") = "C" Or Session("strOperacao") = "A" Or Session("strOperacao") = "E") Then ' Consultar | Alterar | Excluir
                        LoadData()
                    End If

                    If (Session("strOperacao") = "C") Then ' Consultar
                        btnGravar.Enabled = False
                    End If

                Catch ex As Exception
                    lblMsg.Text = ex.Message
                End Try

            End If

        End If

    End Sub

    ' Step 02
    Private Function LoadHolding(ByVal strConn As String) As Boolean

        ' CAMG Informática | Marcelo Barcelos | 26.06.2010 08h


        LoadHolding = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String = "SELECT CodigoDaHolding, NomeDaHolding" & _
                         " FROM Holding;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                If (theSqlDataReader.Read() = True) Then
                    lblHolding.Text = theSqlDataReader(1)
                    lblHolding.ToolTip = theSqlDataReader(0)
                    LoadHolding = True
                Else
                    lblMsg.Text = "Holding não cadastrada no Sistema."
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using


    End Function

    '' Step 03
    Private Sub FillDDL()

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodigoDaCia, NomeDaCia" & _
                     " FROM Companhia" & _
                     " WHERE CodigoDaHolding = '" & lblHolding.ToolTip & "'" & _
                     " ORDER BY CodigoDaCia;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                ddlCompanhia.DataTextField = "NomeDaCia"
                ddlCompanhia.DataValueField = "CodigoDaCia"
                ddlCompanhia.DataSource = mydatatable
                ddlCompanhia.DataBind()

                ddlCompanhia.Items.Insert(0, "")

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using


        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodFornecedorServico, RazaoSocialFornecedorServico AS FornecedorServico" & _
                         " FROM fornecedorservico WHERE CodTipoFornecedorServico = 2" & _
                         " ORDER BY RazaoSocialFornecedorServico;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)

                ddlFornecedorServico.DataTextField = "FornecedorServico"
                ddlFornecedorServico.DataValueField = "CodFornecedorServico"
                ddlFornecedorServico.DataSource = mydatatable
                ddlFornecedorServico.DataBind()

                ddlFornecedorServico.Items.Insert(0, "")

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using


        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodigoDaMoeda, SiglaDaMoeda + ' - ' + NomeDaMoeda AS Moeda" & _
                 " FROM Moeda" & _
                 " ORDER BY SiglaDaMoeda;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                ddlMoeda.DataTextField = "Moeda"
                ddlMoeda.DataValueField = "CodigoDaMoeda"
                ddlMoeda.DataSource = mydatatable
                ddlMoeda.DataBind()

                ddlMoeda.Items.Insert(0, "")


                ddlMoedaFOB.DataTextField = "Moeda"
                ddlMoedaFOB.DataValueField = "CodigoDaMoeda"
                ddlMoedaFOB.DataSource = mydatatable
                ddlMoedaFOB.DataBind()
                ddlMoedaFOB.Items.Insert(0, "")

                ddlMoedaFrete.DataTextField = "Moeda"
                ddlMoedaFrete.DataValueField = "CodigoDaMoeda"
                ddlMoedaFrete.DataSource = mydatatable
                ddlMoedaFrete.DataBind()
                ddlMoedaFrete.Items.Insert(0, "")

                ddlMoedaSeguro.DataTextField = "Moeda"
                ddlMoedaSeguro.DataValueField = "CodigoDaMoeda"
                ddlMoedaSeguro.DataSource = mydatatable
                ddlMoedaSeguro.DataBind()
                ddlMoedaSeguro.Items.Insert(0, "")

                ddlMoedaAcrescimo.DataTextField = "Moeda"
                ddlMoedaAcrescimo.DataValueField = "CodigoDaMoeda"
                ddlMoedaAcrescimo.DataSource = mydatatable
                ddlMoedaAcrescimo.DataBind()
                ddlMoedaAcrescimo.Items.Insert(0, "")

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Sub

    '' Step 04
    Private Sub LoadData()

        Dim intQtdeItems As Integer = 0

        ddlCompanhia.Enabled = False
        ddlEstabelecimento.Enabled = False
        ddlFornecedorServico.Enabled = False
        txtNumeroDocumento.Enabled = False
        ddlDI.Enabled = False
        txtDataDocumento.Enabled = False
        txtDataLancamento.Enabled = False
        txtDataRegistro.Enabled = False
        ddlMoeda.Enabled = False

        If (Session("strOperacao").ToString = "C" Or Session("strOperacao").ToString = "E") Then ' Consultar | Excluir


        End If

        ddlCompanhia.SelectedValue = Session("strCodigoDaCia").ToString
        FillDDLEstabelecimento()

        ddlEstabelecimento.SelectedValue = Session("strCodigoDoEstabelecimento").ToString
        LoadUFDestino()

        ddlFornecedorServico.SelectedValue = Session("strCodFornecedorServico").ToString
        LoadUFOrigem()

        txtNumeroDocumento.Text = Session("strNumeroDocumento").ToString
        txtDataDocumento.Text = Session("strDataDocumento").ToString

        'FillDDLDI()
        ddlDI.Text = Session("strDI").ToString

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)


            Dim theQueryString As String = "SELECT" & _
                     " DataDocumento," & _
                     " DataLancamento, DataRegistro, CodMoeda, ValorTaxaMoedaOriginal, ValorTaxaConversao, ValorFob, CodMoedaFob, ValorTaxaFob, ValorRealFob, ValorUSFob, PercentFob," & _
                     " ValorFrete, CodMoedaFrete, ValorTaxaFrete, ValorRealFrete, ValorUSFrete, PercentFrete, " & _
                     " ValorSeguro, CodMoedaSeguro, ValorTaxaSeguro, ValorRealSeguro, ValorUSSeguro, PercentSeguro, " & _
                     " ValorAcrescimo, CodMoedaAcrescimo, ValorTaxaAcrescimo, ValorRealAcrescimo, ValorUSAcrescimo, PercentAcrescimo"

            theQueryString = theQueryString & _
                     " FROM custoimportacao" & _
                     " WHERE CodigoDaHolding = '" & lblHolding.ToolTip & "'" & _
                     " AND CodigoDaCia = '" & ddlCompanhia.SelectedValue & "'" & _
                     " AND CodigoDoEstabelecimento = '" & ddlEstabelecimento.SelectedValue & "'" & _
                     " AND CodFornecedorServico = '" & ddlFornecedorServico.SelectedValue & "'" & _
                     " AND NumeroDocumento = '" & UCase(Trim(txtNumeroDocumento.Text)) & "';"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try

                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                Do While theSqlDataReader.Read()

                    If Not (IsDBNull(theSqlDataReader!DataLancamento) = True) Then
                        txtDataLancamento.Text = Format(CDate(theSqlDataReader!DataLancamento.ToString), "dd/MM/yyyy")
                    End If

                    If Not (IsDBNull(theSqlDataReader!DataRegistro) = True) Then
                        txtDataRegistro.Text = Format(CDate(theSqlDataReader!DataRegistro.ToString), "dd/MM/yyyy")
                    End If

                    If Not (IsDBNull(theSqlDataReader!CodMoeda) = True) Then
                        ddlMoeda.SelectedValue = theSqlDataReader!CodMoeda
                    End If


                    If Not (IsDBNull(theSqlDataReader!ValorTaxaMoedaOriginal) = True) Then
                        lblTaxaMoedaOriginal.Text = theSqlDataReader!ValorTaxaMoedaOriginal
                    End If

                    If Not (IsDBNull(theSqlDataReader!ValorTaxaConversao) = True) Then
                        lblTaxaConversao.Text = theSqlDataReader!ValorTaxaConversao
                    End If

                    If Not (IsDBNull(theSqlDataReader!ValorFob) = True) Then
                        txtVlFOB.Text = theSqlDataReader!ValorFob
                     End If

                    If Not (IsDBNull(theSqlDataReader!CodMoedaFob) = True) Then
                        ddlMoedaFOB.SelectedValue = theSqlDataReader!CodMoedaFob
                    End If

                    If Not (IsDBNull(theSqlDataReader!ValorTaxaFob) = True) Then
                        lblTxFOB.Text = theSqlDataReader!ValorTaxaFob
                    End If

                    If Not (IsDBNull(theSqlDataReader!ValorRealFob) = True) Then
                        lblVlrFOB.Text = theSqlDataReader!ValorRealFob
                    End If

                    If Not (IsDBNull(theSqlDataReader!ValorUSFob) = True) Then
                        lblVluFOB.Text = theSqlDataReader!ValorUSFob
                    End If

                    If Not (IsDBNull(theSqlDataReader!PercentFob) = True) Then
                        lblPerFOB.Text = theSqlDataReader!PercentFob
                    End If



                    If Not (IsDBNull(theSqlDataReader!ValorFrete) = True) Then
                        txtVlFrete.Text = theSqlDataReader!ValorFrete
                    End If

                    If Not (IsDBNull(theSqlDataReader!CodMoedaFrete) = True) Then
                        ddlMoedaFrete.SelectedValue = theSqlDataReader!CodMoedaFrete
                    End If

                    If Not (IsDBNull(theSqlDataReader!ValorTaxaFrete) = True) Then
                        lblTxFrete.Text = theSqlDataReader!ValorTaxaFrete
                    End If

                    If Not (IsDBNull(theSqlDataReader!ValorRealFrete) = True) Then
                        lblVlrFrete.Text = theSqlDataReader!ValorRealFrete
                    End If

                    If Not (IsDBNull(theSqlDataReader!ValorUSFrete) = True) Then
                        lblVluFrete.Text = theSqlDataReader!ValorUSFrete
                    End If

                    If Not (IsDBNull(theSqlDataReader!PercentFrete) = True) Then
                        lblPerFrete.Text = theSqlDataReader!PercentFrete
                    End If




                    If Not (IsDBNull(theSqlDataReader!ValorSeguro) = True) Then
                        txtVlSeg.Text = theSqlDataReader!ValorSeguro
                    End If

                    If Not (IsDBNull(theSqlDataReader!CodMoedaSeguro) = True) Then
                        ddlMoedaSeguro.SelectedValue = theSqlDataReader!CodMoedaSeguro
                    End If

                    If Not (IsDBNull(theSqlDataReader!ValorTaxaSeguro) = True) Then
                        lblTxSeg.Text = theSqlDataReader!ValorTaxaSeguro
                    End If

                    If Not (IsDBNull(theSqlDataReader!ValorRealSeguro) = True) Then
                        lblVlrSeg.Text = theSqlDataReader!ValorRealSeguro
                    End If

                    If Not (IsDBNull(theSqlDataReader!ValorUSSeguro) = True) Then
                        lblVluSeg.Text = theSqlDataReader!ValorUSSeguro
                    End If

                    If Not (IsDBNull(theSqlDataReader!PercentSeguro) = True) Then
                        lblPerSeg.Text = theSqlDataReader!PercentSeguro
                    End If



                    If Not (IsDBNull(theSqlDataReader!ValorAcrescimo) = True) Then
                        txtVlAcr.Text = theSqlDataReader!ValorAcrescimo
                    End If

                    If Not (IsDBNull(theSqlDataReader!CodMoedaAcrescimo) = True) Then
                        ddlMoedaAcrescimo.SelectedValue = theSqlDataReader!CodMoedaAcrescimo
                    End If

                    If Not (IsDBNull(theSqlDataReader!ValorTaxaAcrescimo) = True) Then
                        lblTxAcr.Text = theSqlDataReader!ValorTaxaAcrescimo
                    End If

                    If Not (IsDBNull(theSqlDataReader!ValorRealAcrescimo) = True) Then
                        lblVlrAcr.Text = theSqlDataReader!ValorRealAcrescimo
                    End If

                    If Not (IsDBNull(theSqlDataReader!ValorUSAcrescimo) = True) Then
                        lblVluAcr.Text = theSqlDataReader!ValorUSAcrescimo
                    End If

                    If Not (IsDBNull(theSqlDataReader!PercentAcrescimo) = True) Then
                        lblPerAcr.Text = theSqlDataReader!PercentAcrescimo
                    End If

                Loop

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using
        Totalizador()

    End Sub

    Private Sub LoadUFOrigem()


        lblUFOrigem.Text = ""

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)


            Dim theQueryString As String = "SELECT" & _
                     " A.CodigoDaUF," & _
                     " B.SiglaDaUF"

            theQueryString = theQueryString & _
                     " FROM FornecedorServico A, uf B" & _
                     " WHERE A.CodigoDaUF = B.CodigoDaUF AND A.CodFornecedorServico = '" & ddlFornecedorServico.SelectedValue & "';"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try

                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                Do While theSqlDataReader.Read()

                    If (IsDBNull(theSqlDataReader!SiglaDaUF) = True) Then
                        lblMsg.Text = "Despachante sem UF informada."
                    Else
                        lblUFOrigem.Text = theSqlDataReader!SiglaDaUF
                        lblUFOrigem.ToolTip = theSqlDataReader!CodigoDaUF
                    End If

                Loop

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Sub

    Private Sub LoadUFDestino()

        lblUFDestino.Text = ""

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String = "SELECT A.CodigoDaUF, B.SiglaDaUF"

            theQueryString = theQueryString & _
                     " FROM Estabelecimento A, uf B" & _
                     " WHERE A.CodigoDaUF = B.CodigoDaUF AND A.CodigoDaHolding = '" & lblHolding.ToolTip & "'" & _
                     " AND A.CodigoDaCia = '" & ddlCompanhia.SelectedValue & "'" & _
                     " AND A.CodigoDoEstabelecimento = '" & ddlEstabelecimento.SelectedValue & "';"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try

                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                Do While theSqlDataReader.Read()

                    If Not (IsDBNull(theSqlDataReader!SiglaDaUF) = True) Then
                        lblUFDestino.Text = theSqlDataReader!SiglaDaUF
                        lblUFDestino.ToolTip = theSqlDataReader!CodigoDaUF
                    Else
                        lblMsg.Text = "Estabelecimento sem UF informada."
                    End If

                Loop

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using


    End Sub


    '' Step 05
    Protected Sub ddlCompanhia_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlCompanhia.SelectedIndexChanged

        FillDDLEstabelecimento()

    End Sub

    '' Step 06
    Private Sub FillDDLEstabelecimento()

        ' CAMG Informática | Marcelo Barcelos | 05.01.2010 16h45

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodigoDoEstabelecimento, CodigoDoEstabelecimento + ' - ' + RazaoSocialDoEstabelecimento AS Estabelecimento" & _
                     " FROM Estabelecimento" & _
                     " WHERE CodigoDaHolding = '" & lblHolding.ToolTip & "'" & _
                     " AND CodigoDaCia = '" & ddlCompanhia.SelectedValue & "'" & _
                     " ORDER BY CodigoDoEstabelecimento;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                ddlEstabelecimento.DataTextField = "Estabelecimento"
                ddlEstabelecimento.DataValueField = "CodigoDoEstabelecimento"
                ddlEstabelecimento.DataSource = mydatatable
                ddlEstabelecimento.DataBind()

                ddlEstabelecimento.Items.Insert(0, "")
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Sub

    '' Step 07
    'Private Sub FillDDLDI()

    '    ' CAMG Informática | Marcelo Barcelos | 05.01.2010 16h45

    '    Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

    '        Dim mydatatable As New DataTable
    '        Dim theQueryString As String

    '        theQueryString = "SELECT DI" & _
    '                 " FROM DIHeader" & _
    '                 " WHERE CodigoDaHolding = '" & lblHolding.ToolTip & "'" & _
    '                 " AND CodigoDaCia = '" & ddlCompanhia.SelectedValue & "'" & _
    '                 " AND CodigoDoEstabelecimento = '" & ddlEstabelecimento.SelectedValue & "'"

    '        If (Session("strOperacao") = "I") Then ' Incluir
    '            theQueryString = theQueryString & _
    '                     " AND CodSituacaoDI IN (1, 2)" & _
    '                     " AND DI NOT IN (SELECT DI FROM ControleDespesasHeader WHERE di is NOT NULL)"
    '        End If

    '        theQueryString = theQueryString & _
    '                 " ORDER BY DI;"

    '        Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
    '        theSqlCommand.CommandText = theQueryString

    '        Try
    '            theSqlConnection.Open()
    '            Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

    '            mydatatable.Load(theSqlDataReader)
    '            ddlDI.DataTextField = "DI"
    '            ddlDI.DataValueField = "DI"
    '            ddlDI.DataSource = mydatatable
    '            ddlDI.DataBind()

    '            ddlDI.Items.Insert(0, "")
    '            theSqlDataReader.Close()
    '        Catch ex As Exception
    '            'theSqlDataReader.Close()
    '            lblMsg.Text = "Msg DB: " & ex.Message
    '        End Try

    '    End Using

    'End Sub

    '' Step 08
    '' Step 09
    Protected Sub btnGravar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnGravar.Click

        Dim ConnectionTest As String
        ConnectionTest = "A"

        lblMsg.Text = ""


        If (Session("strOperacao") = "I") Then ' Incluir

            If Not (DataValid() = True) Then
                Exit Sub
            End If

            If (CodeExist(ConnectionTest) = True) Then
                Exit Sub
            End If

            If Not (AddRecord(ConnectionTest) = True) Then
                Exit Sub
            End If

        ElseIf (Session("strOperacao") = "A") Then ' Alterar

            If Not (DataValid() = True) Then
                Exit Sub
            End If

            If Not (EditRecord(ConnectionTest) = True) Then
                Exit Sub
            End If

        ElseIf (Session("strOperacao") = "E") Then ' Excluir

            If (VerifyData(ConnectionTest) = True) Then
                Exit Sub
            End If

            If Not (DeleteRecord(ConnectionTest) = True) Then
                Exit Sub
            End If

        End If

        lblMsg.ForeColor = Drawing.Color.Green
        lblMsg.Text = "Operação realizada com sucesso. Clique no botão 'OK'"

        btnGravar.Enabled = False
        btnVoltar.Text = "OK"

    End Sub

    '' Step 10
    Private Function DataValid() As Boolean

        DataValid = False

        If (ddlCompanhia.Text = "") Then
            lblMsg.Text = "Companhia deve ser selecionada."
            Exit Function
        End If

        If (ddlEstabelecimento.Text = "") Then
            lblMsg.Text = "Estabelecimento deve ser selecionado."
            Exit Function
        End If

        If (ddlFornecedorServico.Text = "") Then
            lblMsg.Text = "Despachante deve ser selecionado."
            Exit Function
        End If

        If (Trim(txtNumeroDocumento.Text) = "") Then
            lblMsg.Text = "Número do Documento deve ser preenchido."
            Exit Function
        End If

        If (Trim(txtDataDocumento.Text) = "") Then
            lblMsg.Text = "Data do Documento deve ser preenchida."
            Exit Function
        End If

        If Not IsDate(txtDataDocumento.Text) = True Then
            lblMsg.Text = "Data do Documento inválida."
            Exit Function
        End If

        If (Trim(txtDataLancamento.Text) = "") Then
            lblMsg.Text = "Data de Lançamento deve ser preenchida."
            Exit Function
        End If

        If Not IsDate(txtDataLancamento.Text) = True Then
            lblMsg.Text = "Data de Lançamento inválida."
            Exit Function
        End If

        If (CDate(txtDataLancamento.Text) < txtDataDocumento.Text) Then
            lblMsg.Text = "Data de Lançamento deve ser igual ou posterior à Data do Documento."
            Exit Function
        End If

        If (ddlDI.Text = "") Then
            lblMsg.Text = "DI deve ser selecionada."
            Exit Function
        End If


        DataValid = True

    End Function

    '' Step 11
    Private Function CodeExist(ByVal strConn As String) As Boolean

        CodeExist = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String
            theQueryString = "SELECT NumeroDocumento" & _
                     " FROM custoimportacao" & _
                     " WHERE CodigoDaHolding = '" & lblHolding.ToolTip & "'" & _
                     " AND CodigoDaCia = '" & ddlCompanhia.SelectedValue & "'" & _
                     " AND CodigoDoEstabelecimento = '" & ddlEstabelecimento.SelectedValue & "'" & _
                     " AND CodFornecedorServico = '" & ddlFornecedorServico.SelectedValue & "'" & _
                     " AND NumeroDocumento = '" & UCase(Trim(txtNumeroDocumento.Text)) & "';"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                If (theSqlDataReader.Read()) Then
                    CodeExist = True
                    lblMsg.Text = "Número do Documento " & txtNumeroDocumento.Text & " já existe no Sistema."
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                CodeExist = True
                'theSqlDataReader.Connection.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Function

    '' Step 12
    '' Step 13
    Private Function AddRecord(ByVal strConn As String) As Boolean

        Dim strDataDocumento As String
        Dim strDataLancamento As String
        Dim strDataRegistro As String
        Dim nDiaCreated As String
        Dim nMesCreated As String
        Dim nAnoCreated As String
        Dim nLastUpdated As String

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            'Dim dAnoVigente As String
            'Dim i As Long

            Dim theQueryString As String = "SELECT CONVERT(CHAR(10), GETDATE(), 103) AS DATADOSERVIDOR"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                Do While theSqlDataReader.Read()
                    Session("dDTAno") = Trim(theSqlDataReader(0))
                Loop
                theSqlDataReader.Close()
            Catch ex As Exception
                Console.WriteLine(ex.Message)
            End Try

        End Using

        nDiaCreated = Mid(Session("dDTAno"), 1, 2)
        nMesCreated = Mid(Session("dDTAno"), 4, 2)
        nAnoCreated = Mid(Session("dDTAno"), 7, 4)
        nLastUpdated = nDiaCreated & "/" & nMesCreated & "/" & nAnoCreated

        If (IsDate(nLastUpdated)) Then
            nLastUpdated = "'" & FormatMediumDate(nLastUpdated) & "'"
        Else
            nLastUpdated = "Null"
        End If

        AddRecord = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            'strDataDocumento = Format(CDate(txtDataDocumento.Text), "yyyy-MM-dd HH:mm:ss")
            'strDataLancamento = Format(CDate(txtDataLancamento.Text), "yyyy-MM-dd HH:mm:ss")

            strDataDocumento = "'" & FormatMediumDate(txtDataDocumento.Text) & "'"
            strDataLancamento = "'" & FormatMediumDate(txtDataLancamento.Text) & "'"
            strDataRegistro = "'" & FormatMediumDate(txtDataRegistro.Text) & "'"

            lblTaxaMoedaOriginal.Text = Replace(lblTaxaMoedaOriginal.Text, ",", ".")
            lblTaxaConversao.Text = Replace(lblTaxaConversao.Text, ",", ".")
            'txtAliquotaICMS.Text = Replace(txtAliquotaICMS.Text, ",", ".")
            'txtValorTotalDespesasICMS.Text = Replace(txtValorTotalDespesasICMS.Text, ",", ".")
            'txtValorOutrosDebitos.Text = Replace(txtValorOutrosDebitos.Text, ",", ".")
            'txtValorOutrosCreditos.Text = Replace(txtValorOutrosCreditos.Text, ",", ".")
            'txtValorTSA.Text = Replace(txtValorTSA.Text, ",", ".")
            'txtValorFTI.Text = Replace(txtValorFTI.Text, ",", ".")

            Dim theQueryString As String
            theQueryString = "INSERT INTO custoimportacao" & _
                     " (" & _
                     " CodigoDaHolding," & _
                     " CodigoDaCia," & _
                     " CodigoDoEstabelecimento," & _
                     " CodFornecedorServico," & _
                     " NumeroDocumento," & _
                     " DataDocumento," & _
                     " DataLancamento," & _
                     " DI," & _
                     " DataRegistro," & _
                     " CodMoeda," & _
                     " ValorTaxaMoedaOriginal," & _
                     " ValorTaxaConversao," & _
                     " CreateUser," & _
                     " Created" & _
                     " )"

            theQueryString = theQueryString & _
                     " VALUES " & _
                     " (" & _
                     " '" & lblHolding.ToolTip & "'," & _
                     " '" & ddlCompanhia.SelectedValue & "'," & _
                     " '" & ddlEstabelecimento.SelectedValue & "'," & _
                     " '" & ddlFornecedorServico.SelectedValue & "'," & _
                     " '" & txtNumeroDocumento.Text & "'," & _
                     " " & strDataDocumento & "," & _
                     " " & strDataLancamento & "," & _
                     " '" & ddlDI.Text & "'," & _
                     " " & strDataRegistro & "," & _
                     " '" & ddlMoeda.SelectedValue & "'," & _
                     " " & lblTaxaMoedaOriginal.Text & "," & _
                     " " & lblTaxaConversao.Text & "," & _
                     " '" & Session("sNM_USERNICKNAME").ToString & "'," & _
                     " " & nLastUpdated & "" & _
                     " );"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                theSqlDataReader.Close()
                AddRecord = True
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Function

    '' Step 14
    Private Function EditRecord(ByVal strConn As String) As Boolean

        Dim strDataDocumento As String
        Dim strDataLancamento As String
        Dim strDataRegistro As String
        Dim nDiaCreated As String
        Dim nMesCreated As String
        Dim nAnoCreated As String
        Dim nLastUpdated As String


        Dim strValorFob As String
        Dim strValorTaxaFob As String
        Dim strValorRealFob As String
        Dim strValorUSFob As String
        Dim strPercentFob As String

        Dim strValorFrete As String
        Dim strValorTaxaFrete As String
        Dim strValorRealFrete As String
        Dim strValorUSFrete As String
        Dim strPercentFrete As String

        Dim strValorSeguro As String
        Dim strValorTaxaSeguro As String
        Dim strValorRealSeguro As String
        Dim strValorUSSeguro As String
        Dim strPercentSeguro As String

        Dim strValorAcrescimo As String
        Dim strValorTaxaAcrescimo As String
        Dim strValorRealAcrescimo As String
        Dim strValorUSAcrescimo As String
        Dim strPercentAcrescimo As String


        Dim strValorTotalReal As String
        Dim strValorTotalUS As String
        Dim strValorTotalPercent As String


        If Not IsNumeric(txtVlFOB.Text) = True Then
            strValorTotalReal = "Null"
        Else
            strValorTotalReal = Replace(lblTotVl.Text, ",", ".")
        End If

        If Not IsNumeric(lblTotVu.Text) = True Then
            strValorTotalUS = "Null"
        Else
            strValorTotalUS = Replace(lblTotVu.Text, ",", ".")
        End If

        If Not IsNumeric(lblTotPer.Text) = True Then
            strValorTotalPercent = "Null"
        Else
            strValorTotalPercent = Replace(lblTotPer.Text, ",", ".")
        End If

        If Not IsNumeric(txtVlFOB.Text) = True Then
            strValorFob = "Null"
        Else
            strValorFob = Replace(txtVlFOB.Text, ",", ".")
        End If

        If Not IsNumeric(lblTxFOB.Text) = True Then
            strValorTaxaFob = "Null"
        Else
            strValorTaxaFob = Replace(lblTxFOB.Text, ",", ".")
        End If

        If Not IsNumeric(lblVlrFOB.Text) = True Then
            strValorRealFob = "Null"
        Else
            strValorRealFob = Replace(lblVlrFOB.Text, ",", ".")
        End If

        If Not IsNumeric(lblVluFOB.Text) = True Then
            strValorUSFob = "Null"
        Else
            strValorUSFob = Replace(lblVluFOB.Text, ",", ".")
        End If

        If Not IsNumeric(lblPerFOB.Text) = True Then
            strPercentFob = "Null"
        Else
            strPercentFob = Replace(lblPerFOB.Text, ",", ".")
        End If




        If Not IsNumeric(txtVlFrete.Text) = True Then
            strValorFrete = "Null"
        Else
            strValorFrete = Replace(txtVlFrete.Text, ",", ".")
        End If

        If Not IsNumeric(lblTxFrete.Text) = True Then
            strValorTaxaFrete = "Null"
        Else
            strValorTaxaFrete = Replace(lblTxFrete.Text, ",", ".")
        End If

        If Not IsNumeric(lblVlrFrete.Text) = True Then
            strValorRealFrete = "Null"
        Else
            strValorRealFrete = Replace(lblVlrFrete.Text, ",", ".")
        End If

        If Not IsNumeric(lblVluFrete.Text) = True Then
            strValorUSFrete = "Null"
        Else
            strValorUSFrete = Replace(lblVluFrete.Text, ",", ".")
        End If

        If Not IsNumeric(lblPerFrete.Text) = True Then
            strPercentFrete = "Null"
        Else
            strPercentFrete = Replace(lblPerFrete.Text, ",", ".")
        End If


        If Not IsNumeric(txtVlSeg.Text) = True Then
            strValorSeguro = "Null"
        Else
            strValorSeguro = Replace(txtVlSeg.Text, ",", ".")
        End If

        If Not IsNumeric(lblTxSeg.Text) = True Then
            strValorTaxaSeguro = "Null"
        Else
            strValorTaxaSeguro = Replace(lblTxSeg.Text, ",", ".")
        End If

        If Not IsNumeric(lblVlrSeg.Text) = True Then
            strValorRealSeguro = "Null"
        Else
            strValorRealSeguro = Replace(lblVlrSeg.Text, ",", ".")
        End If

        If Not IsNumeric(lblVluSeg.Text) = True Then
            strValorUSSeguro = "Null"
        Else
            strValorUSSeguro = Replace(lblVluSeg.Text, ",", ".")
        End If

        If Not IsNumeric(lblPerSeg.Text) = True Then
            strPercentSeguro = "Null"
        Else
            strPercentSeguro = Replace(lblPerSeg.Text, ",", ".")
        End If



        If Not IsNumeric(txtVlAcr.Text) = True Then
            strValorAcrescimo = "Null"
        Else
            strValorAcrescimo = Replace(txtVlAcr.Text, ",", ".")
        End If

        If Not IsNumeric(lblTxAcr.Text) = True Then
            strValorTaxaAcrescimo = "Null"
        Else
            strValorTaxaAcrescimo = Replace(lblTxAcr.Text, ",", ".")
        End If

        If Not IsNumeric(lblVlrAcr.Text) = True Then
            strValorRealAcrescimo = "Null"
        Else
            strValorRealAcrescimo = Replace(lblVlrAcr.Text, ",", ".")
        End If

        If Not IsNumeric(lblVluAcr.Text) = True Then
            strValorUSAcrescimo = "Null"
        Else
            strValorUSAcrescimo = Replace(lblVluAcr.Text, ",", ".")
        End If

        If Not IsNumeric(lblPerAcr.Text) = True Then
            strPercentAcrescimo = "Null"
        Else
            strPercentAcrescimo = Replace(lblPerAcr.Text, ",", ".")
        End If


        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            'Dim dAnoVigente As String
            'Dim i As Long

            Dim theQueryString As String = "SELECT CONVERT(CHAR(10), GETDATE(), 103) AS DATADOSERVIDOR"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                Do While theSqlDataReader.Read()
                    Session("dDTAno") = Trim(theSqlDataReader(0))
                Loop
                theSqlDataReader.Close()
            Catch ex As Exception
                Console.WriteLine(ex.Message)
            End Try

        End Using

        nDiaCreated = Mid(Session("dDTAno"), 1, 2)
        nMesCreated = Mid(Session("dDTAno"), 4, 2)
        nAnoCreated = Mid(Session("dDTAno"), 7, 4)
        nLastUpdated = nDiaCreated & "/" & nMesCreated & "/" & nAnoCreated

        If (IsDate(nLastUpdated)) Then
            nLastUpdated = "'" & FormatMediumDate(nLastUpdated) & "'"
        Else
            nLastUpdated = "Null"
        End If

        EditRecord = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)


            strDataDocumento = "'" & FormatMediumDate(txtDataDocumento.Text) & "'"
            strDataLancamento = "'" & FormatMediumDate(txtDataLancamento.Text) & "'"
            strDataRegistro = "'" & FormatMediumDate(txtDataRegistro.Text) & "'"

            lblTaxaMoedaOriginal.Text = Replace(lblTaxaMoedaOriginal.Text, ",", ".")
            lblTaxaConversao.Text = Replace(lblTaxaConversao.Text, ",", ".")
            'Round(CDbl(txtValorFreteReal.Text), 2)

            Dim theQueryString As String
            theQueryString = "UPDATE custoimportacao SET" & _
                     " DataDocumento = " & strDataDocumento & "," & _
                     " DataLancamento = " & strDataLancamento & "," & _
                     " DataRegistro = " & strDataRegistro & "," & _
                     " CodMoeda = '" & ddlMoeda.SelectedValue & "'," & _
                     " ValorFob = " & strValorFob & "," & _
                     " CodMoedaFob = '" & ddlMoedaFOB.SelectedValue & "'," & _
                     " ValorTaxaFob = " & strValorTaxaFob & "," & _
                     " ValorRealFob = " & strValorRealFob & "," & _
                     " ValorUSFob = " & strValorUSFob & "," & _
                     " PercentFob = " & strPercentFob & "," & _
                     " ValorFrete = " & strValorFrete & "," & _
                     " CodMoedaFrete = '" & ddlMoedaFrete.SelectedValue & "'," & _
                     " ValorTaxaFrete = " & strValorTaxaFrete & "," & _
                     " ValorRealFrete = " & strValorRealFrete & "," & _
                     " ValorUSFrete = " & strValorUSFrete & "," & _
                     " PercentFrete = " & strPercentFrete & "," & _
                     " ValorSeguro = " & strValorSeguro & "," & _
                     " CodMoedaSeguro = '" & ddlMoedaSeguro.SelectedValue & "'," & _
                     " ValorTaxaSeguro = " & strValorTaxaSeguro & "," & _
                     " ValorRealSeguro = " & strValorRealSeguro & "," & _
                     " ValorUSSeguro = " & strValorUSSeguro & "," & _
                     " PercentSeguro = " & strPercentSeguro & "," & _
                     " ValorAcrescimo = " & strValorAcrescimo & "," & _
                     " CodMoedaAcrescimo = '" & ddlMoedaAcrescimo.SelectedValue & "'," & _
                     " ValorTaxaAcrescimo = " & strValorTaxaAcrescimo & "," & _
                     " ValorRealAcrescimo = " & strValorRealAcrescimo & "," & _
                     " ValorUSAcrescimo = " & strValorUSAcrescimo & "," & _
                     " PercentAcrescimo = " & strPercentAcrescimo & "," & _
                     " ValorTaxaMoedaOriginal = " & lblTaxaMoedaOriginal.Text & "," & _
                     " ValorTaxaConversao = " & lblTaxaConversao.Text & "," & _
                     " ValorTotalReal = " & strValorTotalReal & "," & _
                     " ValorTotalUS = " & strValorTotalUS & "," & _
                     " ValorTotalPercent  = " & strValorTotalPercent & ","



            theQueryString = theQueryString & _
                     " LastUser = '" & Session("sNM_USERNICKNAME").ToString & "'," & _
                     " LastUpdated = " & nLastUpdated & ""

            theQueryString = theQueryString & _
                     " WHERE CodigoDaHolding = '" & lblHolding.ToolTip & "'" & _
                     " AND CodigoDaCia = '" & ddlCompanhia.SelectedValue & "'" & _
                     " AND CodigoDoEstabelecimento = '" & ddlEstabelecimento.SelectedValue & "'" & _
                     " AND CodFornecedorServico = '" & ddlFornecedorServico.SelectedValue & "'" & _
                     " AND NumeroDocumento = '" & UCase(Trim(txtNumeroDocumento.Text)) & "';"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                theSqlDataReader.Close()
                EditRecord = True
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Function

    '' Step 15
    Private Function VerifyData(ByVal strConn As String) As Boolean

        VerifyData = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String
            theQueryString = "SELECT NumeroDocumento" & _
                         " FROM custoimportacao" & _
                         " WHERE CodigoDaHolding = '" & lblHolding.ToolTip & "'" & _
                         " AND CodigoDaCia = '" & ddlCompanhia.SelectedValue & "'" & _
                         " AND CodigoDoEstabelecimento = '" & ddlEstabelecimento.SelectedValue & "'" & _
                         " AND CodFornecedorServico = '" & ddlFornecedorServico.SelectedValue & "'" & _
                         " AND NumeroDocumento = '" & UCase(Trim(txtNumeroDocumento.Text)) & "';"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                If (theSqlDataReader.Read()) Then
                    VerifyData = True
                    lblMsg.Text = "Exclusão não permitida, existe relação de Despesas com Linhas."
                End If

                theSqlDataReader.Close()
            Catch ex As Exception
                VerifyData = True
                'theSqlDataReader.Connection.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Function

    '' Step 16
    Private Function DeleteRecord(ByVal strConn As String) As Boolean

        DeleteRecord = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String
            theQueryString = "DELETE FROM custoimportacao" & _
                         " WHERE CodigoDaHolding = '" & lblHolding.ToolTip & "'" & _
                         " AND CodigoDaCia = '" & ddlCompanhia.SelectedValue & "'" & _
                         " AND CodigoDoEstabelecimento = '" & ddlEstabelecimento.SelectedValue & "'" & _
                         " AND CodFornecedorServico = '" & ddlFornecedorServico.SelectedValue & "'" & _
                         " AND NumeroDocumento = '" & UCase(Trim(txtNumeroDocumento.Text)) & "';"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                theSqlDataReader.Close()
                DeleteRecord = True
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Function

    '' Step 17
    Protected Sub btnVoltar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnVoltar.Click

        Response.Redirect("CustoDeImportacao.aspx")

    End Sub

    Protected Sub ddlEstabelecimento_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlEstabelecimento.SelectedIndexChanged

        lblMsg.Text = ""

        LoadUFDestino()

        'FillDDLDI()

    End Sub

    Protected Sub ddlFornecedorServico_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlFornecedorServico.SelectedIndexChanged

        lblMsg.Text = ""

        LoadUFOrigem()

    End Sub

    Public Function FormatMediumDate(ByVal DateValue As String) As String

        Dim strYYYY
        Dim strMM
        Dim strDD

        'Strip the Year
        strYYYY = CStr(DatePart("yyyy", DateValue))

        'Strip the Month	
        strMM = CStr(DatePart("m", DateValue))

        'See if month is a single digit if so display it as 01, 02, 03, etc
        If Len(strMM) = 1 Then strMM = "0" & strMM

        'Strip the Date	
        strDD = CStr(DatePart("d", DateValue))

        'See if the Day is a single digit day if so diaply it as 01, 02, 03, etc
        If Len(strDD) = 1 Then strDD = "0" & strDD

        'return the date formatted as DD/MM/YYY	
        FormatMediumDate = strYYYY & "/" & strMM & "/" & strDD

        'you could return the date many ways just retype the above string
        'FormatMediumDate = strDD & "-" & strMM & "-" & strYYYY
        'FormatMediumDate = strMM & "/" & strDD & "/" & strYYYY
        'FormatMediumDate = strYYYY & "-" & strMM & "-" & strMM

    End Function

    Protected Sub ddlMoeda_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlMoeda.SelectedIndexChanged

        lblMsg.Text = ""
        Try
            lblTaxaMoedaOriginal.Text = ""
            lblTaxaConversao.Text = ""
            'txtValorFOBReal.Text = ""

            If (ddlMoeda.Text <> "") Then
                GetTaxaCambio(ddlMoeda.SelectedValue, txtDataRegistro.Text)
            End If

        Catch ex As Exception
            lblMsg.Text = ex.Message
        End Try

    End Sub

    Private Function GetTaxaCambio(ByVal intCodigoDaMoeda As Integer, ByVal strDtRegistro As String) As String

        Dim strDataCotacao As String = ""

        GetTaxaCambio = ""

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            strDataCotacao = "'" & FormatMediumDate(strDtRegistro) & "'"

            Dim theQueryString As String
            theQueryString = "SELECT TOP 3 DataCotacao, ValorCotacaoVenda" & _
                     " FROM TaxaDeCambio" & _
                     " WHERE CodigoDaMoeda = " & intCodigoDaMoeda & "" & _
                     " AND DataCotacao <= " & strDataCotacao & "" & _
                     " ORDER BY DataCotacao DESC;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                If (theSqlDataReader.HasRows = True) Then
                    Do While theSqlDataReader.Read()
                        lblTaxaMoedaOriginal.Text = theSqlDataReader!ValorCotacaoVenda
                    Loop

                End If

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try
        End Using

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String
            theQueryString = "SELECT TOP 3 DataCotacao, ValorCotacaoVenda" & _
                     " FROM TaxaDeCambio" & _
                     " WHERE CodigoDaMoeda = 220" & _
                     " AND DataCotacao <= " & strDataCotacao & "" & _
                     " ORDER BY DataCotacao DESC;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                If (theSqlDataReader.HasRows = True) Then
                    Do While theSqlDataReader.Read()
                        lblTaxaConversao.Text = theSqlDataReader!ValorCotacaoVenda
                    Loop

                End If

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try
        End Using



    End Function

    Protected Sub ddlMoedaFOB_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlMoedaFOB.SelectedIndexChanged

        lblMsg.Text = ""
        Try
            lblTxFOB.Text = ""
            lblVlrFOB.Text = ""
            lblVluFOB.Text = ""
            lblPerFOB.Text = ""

            If (ddlMoeda.Text <> "") Then
                GetTaxaCambioFOB(ddlMoedaFOB.SelectedValue, txtDataRegistro.Text)
            End If

        Catch ex As Exception
            lblMsg.Text = ex.Message
        End Try

    End Sub

    Private Function GetTaxaCambioFOB(ByVal intCodigoDaMoeda As Integer, ByVal strDtRegistro As String) As String

        Dim strDataCotacao As String = ""

        GetTaxaCambioFOB = ""

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            strDataCotacao = "'" & FormatMediumDate(strDtRegistro) & "'"

            Dim theQueryString As String
            theQueryString = "SELECT TOP 3 DataCotacao, ValorCotacaoVenda" & _
                     " FROM TaxaDeCambio" & _
                     " WHERE CodigoDaMoeda = '" & intCodigoDaMoeda & "'" & _
                     " AND DataCotacao <= " & strDataCotacao & "" & _
                     " ORDER BY DataCotacao DESC;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                If (theSqlDataReader.HasRows = True) Then
                    Do While theSqlDataReader.Read()
                        lblTxFOB.Text = theSqlDataReader!ValorCotacaoVenda
                        lblVlrFOB.Text = Round(CDbl(txtVlFOB.Text * lblTxFOB.Text), 2)
                    Loop

                End If

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try
        End Using

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String
            theQueryString = "SELECT TOP 3 DataCotacao, ValorCotacaoVenda" & _
                     " FROM TaxaDeCambio" & _
                     " WHERE CodigoDaMoeda = '220'" & _
                     " AND DataCotacao <= " & strDataCotacao & "" & _
                     " ORDER BY DataCotacao DESC;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                If (theSqlDataReader.HasRows = True) Then
                    Do While theSqlDataReader.Read()
                        If (intCodigoDaMoeda = "220") Then
                            lblVluFOB.Text = txtVlFOB.Text
                        Else
                            lblVluFOB.Text = Round(CDbl(lblVlrFOB.Text / theSqlDataReader!ValorCotacaoVenda), 2)
                        End If
                    Loop

                End If

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try
        End Using

        Totalizador()

    End Function

    Protected Sub ddlMoedaFrete_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlMoedaFrete.SelectedIndexChanged

        lblMsg.Text = ""
        Try
            lblTxFrete.Text = ""
            lblVlrFrete.Text = ""
            lblVluFrete.Text = ""
            lblPerFrete.Text = ""

            If (ddlMoedaFrete.Text <> "") Then
                GetTaxaCambioFrete(ddlMoedaFrete.SelectedValue, txtDataRegistro.Text)
            End If

        Catch ex As Exception
            lblMsg.Text = ex.Message
        End Try

    End Sub

    Private Function GetTaxaCambioFrete(ByVal intCodigoDaMoeda As Integer, ByVal strDtRegistro As String) As String

        Dim strDataCotacao As String = ""

        GetTaxaCambioFrete = ""

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            strDataCotacao = "'" & FormatMediumDate(strDtRegistro) & "'"

            Dim theQueryString As String
            theQueryString = "SELECT TOP 3 DataCotacao, ValorCotacaoVenda" & _
                     " FROM TaxaDeCambio" & _
                     " WHERE CodigoDaMoeda = '" & intCodigoDaMoeda & "'" & _
                     " AND DataCotacao <= " & strDataCotacao & "" & _
                     " ORDER BY DataCotacao DESC;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                If (theSqlDataReader.HasRows = True) Then
                    Do While theSqlDataReader.Read()
                        lblTxFrete.Text = theSqlDataReader!ValorCotacaoVenda
                        lblVlrFrete.Text = Round(CDbl(txtVlFrete.Text * lblTxFrete.Text), 2)
                    Loop

                End If

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try
        End Using

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String
            theQueryString = "SELECT TOP 3 DataCotacao, ValorCotacaoVenda" & _
                     " FROM TaxaDeCambio" & _
                     " WHERE CodigoDaMoeda = '220'" & _
                     " AND DataCotacao <= " & strDataCotacao & "" & _
                     " ORDER BY DataCotacao DESC;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                If (theSqlDataReader.HasRows = True) Then
                    Do While theSqlDataReader.Read()
                        If (intCodigoDaMoeda = "220") Then
                            lblVluFrete.Text = txtVlFrete.Text
                        Else
                            lblVluFrete.Text = Round(CDbl(lblVlrFrete.Text / theSqlDataReader!ValorCotacaoVenda), 2)
                        End If
                    Loop

                End If

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try
        End Using

        Totalizador()

    End Function

    Protected Sub ddlMoedaSeguro_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlMoedaSeguro.SelectedIndexChanged

        lblMsg.Text = ""
        Try
            lblTxSeg.Text = ""
            lblVlrSeg.Text = ""
            lblVluSeg.Text = ""
            lblPerSeg.Text = ""

            If (ddlMoedaSeguro.Text <> "") Then
                GetTaxaCambioSeguro(ddlMoedaSeguro.SelectedValue, txtDataRegistro.Text)
            End If

        Catch ex As Exception
            lblMsg.Text = ex.Message
        End Try

    End Sub

    Private Function GetTaxaCambioSeguro(ByVal intCodigoDaMoeda As Integer, ByVal strDtRegistro As String) As String

        Dim strDataCotacao As String = ""

        GetTaxaCambioSeguro = ""

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            strDataCotacao = "'" & FormatMediumDate(strDtRegistro) & "'"

            Dim theQueryString As String
            theQueryString = "SELECT TOP 3 DataCotacao, ValorCotacaoVenda" & _
                     " FROM TaxaDeCambio" & _
                     " WHERE CodigoDaMoeda = '" & intCodigoDaMoeda & "'" & _
                     " AND DataCotacao <= " & strDataCotacao & "" & _
                     " ORDER BY DataCotacao DESC;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                If (theSqlDataReader.HasRows = True) Then
                    Do While theSqlDataReader.Read()
                        lblTxSeg.Text = theSqlDataReader!ValorCotacaoVenda
                        lblVlrSeg.Text = Round(CDbl(txtVlSeg.Text * lblTxSeg.Text), 2)
                    Loop

                End If

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try
        End Using

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String
            theQueryString = "SELECT TOP 3 DataCotacao, ValorCotacaoVenda" & _
                     " FROM TaxaDeCambio" & _
                     " WHERE CodigoDaMoeda = '220'" & _
                     " AND DataCotacao <= " & strDataCotacao & "" & _
                     " ORDER BY DataCotacao DESC;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                If (theSqlDataReader.HasRows = True) Then
                    Do While theSqlDataReader.Read()
                        If (intCodigoDaMoeda = "220") Then
                            lblVluSeg.Text = txtVlSeg.Text
                        Else
                            lblVluSeg.Text = Round(CDbl(lblVlrSeg.Text / theSqlDataReader!ValorCotacaoVenda), 2)
                        End If
                    Loop

                End If

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try
        End Using

        Totalizador()

    End Function




    Protected Sub ddlMoedaAcrescimo_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlMoedaAcrescimo.SelectedIndexChanged

        lblMsg.Text = ""
        Try
            lblTxAcr.Text = ""
            lblVlrAcr.Text = ""
            lblVluAcr.Text = ""
            lblPerAcr.Text = ""

            If (ddlMoedaAcrescimo.Text <> "") Then
                GetTaxaCambioAcrescimo(ddlMoedaAcrescimo.SelectedValue, txtDataRegistro.Text)
            End If

        Catch ex As Exception
            lblMsg.Text = ex.Message
        End Try

    End Sub

    Private Function GetTaxaCambioAcrescimo(ByVal intCodigoDaMoeda As Integer, ByVal strDtRegistro As String) As String

        Dim strDataCotacao As String = ""

        GetTaxaCambioAcrescimo = ""

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            strDataCotacao = "'" & FormatMediumDate(strDtRegistro) & "'"

            Dim theQueryString As String
            theQueryString = "SELECT TOP 3 DataCotacao, ValorCotacaoVenda" & _
                     " FROM TaxaDeCambio" & _
                     " WHERE CodigoDaMoeda = '" & intCodigoDaMoeda & "'" & _
                     " AND DataCotacao <= " & strDataCotacao & "" & _
                     " ORDER BY DataCotacao DESC;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                If (theSqlDataReader.HasRows = True) Then
                    Do While theSqlDataReader.Read()
                        lblTxAcr.Text = theSqlDataReader!ValorCotacaoVenda
                        lblVlrAcr.Text = Round(CDbl(txtVlAcr.Text * lblTxAcr.Text), 2)
                    Loop

                End If

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try
        End Using

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String
            theQueryString = "SELECT TOP 3 DataCotacao, ValorCotacaoVenda" & _
                     " FROM TaxaDeCambio" & _
                     " WHERE CodigoDaMoeda = '220'" & _
                     " AND DataCotacao <= " & strDataCotacao & "" & _
                     " ORDER BY DataCotacao DESC;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                If (theSqlDataReader.HasRows = True) Then
                    Do While theSqlDataReader.Read()
                        If (intCodigoDaMoeda = "220") Then
                            lblVluAcr.Text = txtVlAcr.Text
                        Else
                            lblVluAcr.Text = Round(CDbl(lblVlrAcr.Text / theSqlDataReader!ValorCotacaoVenda), 2)
                        End If
                    Loop

                End If

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try
        End Using

        Totalizador()

    End Function

    Private Sub Totalizador()

        If (lblTxFOB.Text = "") Then
            lblTxFOB.Text = "0"
        End If

        If (lblTxFrete.Text = "") Then
            lblTxFrete.Text = "0"
        End If

        If (lblTxSeg.Text = "") Then
            lblTxSeg.Text = "0"
        End If

        If (lblTxAcr.Text = "") Then
            lblTxAcr.Text = "0"
        End If
        lblTotTaxa.Text = CDbl(lblTxFOB.Text) + CDbl(lblTxFrete.Text) + CDbl(lblTxSeg.Text) + CDbl(lblTxAcr.Text)



        If (lblVlrFOB.Text = "") Then
            lblVlrFOB.Text = "0"
        End If

        If (lblVlrFrete.Text = "") Then
            lblVlrFrete.Text = "0"
        End If

        If (lblVlrSeg.Text = "") Then
            lblVlrSeg.Text = "0"
        End If

        If (lblVlrAcr.Text = "") Then
            lblVlrAcr.Text = "0"
        End If
        lblTotVl.Text = CDbl(lblVlrFOB.Text) + CDbl(lblVlrFrete.Text) + CDbl(lblVlrSeg.Text) + CDbl(lblVlrAcr.Text)



        If (lblVluFOB.Text = "") Then
            lblVluFOB.Text = "0"
        End If

        If (lblVluFrete.Text = "") Then
            lblVluFrete.Text = "0"
        End If

        If (lblVluSeg.Text = "") Then
            lblVluSeg.Text = "0"
        End If

        If (lblVluAcr.Text = "") Then
            lblVluAcr.Text = "0"
        End If
        lblTotVu.Text = CDbl(lblVluFOB.Text) + CDbl(lblVluFrete.Text) + CDbl(lblVluSeg.Text) + CDbl(lblVluAcr.Text)


        If (lblPerFOB.Text = "") Then
            lblPerFOB.Text = "0"
        End If

        If (lblPerFrete.Text = "") Then
            lblPerFrete.Text = "0"
        End If

        If (lblPerSeg.Text = "") Then
            lblPerSeg.Text = "0"
        End If

        If (lblPerAcr.Text = "") Then
            lblPerAcr.Text = "0"
        End If
        lblTotPer.Text = CDbl(lblPerFOB.Text) + CDbl(lblPerFrete.Text) + CDbl(lblPerSeg.Text) + CDbl(lblPerAcr.Text)

    End Sub


End Class

